﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TIS.DAO
{
    public class UserDAO
    {
        UserInfo _userInfo = new UserInfo();
        private static readonly String Url = ConfigurationManager.AppSettings["Main.ConnectionString"];
        public UserDAO()
        {

        }

        private SqlConnection _connection;
        private SqlConnection GetConnection()
        {
            _connection = new SqlConnection(Url);
            if (_connection.State == ConnectionState.Closed)
            {
                _connection.Open();
            }

            return _connection;
        }

        public UserInfo GetInfo(string user, string pass)
        {
            try
            {
                _connection = GetConnection();
                var spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@username", user);
                spr[1] = new SqlParameter("@pass", pass);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM Users WHERE Username = @username AND Password = @pass ", spr);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        _userInfo.UserId = int.Parse(row["UserId"].ToString());
                        _userInfo.Username = row["Username"].ToString();
                        _userInfo.Password = row["Password"].ToString();
                        _userInfo.FullName = row["FullName"].ToString();
                        _userInfo.Email = row["Email"].ToString();
                        _userInfo.Phone = row["Phone"].ToString();
                        _userInfo.Address = row["Address"].ToString();
                        _userInfo.DateCreate = DateTime.Parse(row["DateCreate"].ToString());
                        _userInfo.Role = Convert.ToInt32(row["Role"].ToString());
                        _userInfo.Status = Convert.ToInt32(row["Status"].ToString());
                    }
                    return _userInfo;
                }
                return null;

            }
            finally
            {
                _connection.Close();
            }

        }

        public UserInfo Select(string user)
        {

            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@Username", user);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.StoredProcedure, "SP_Select_User", spr);

                DataTable dtUser = ds.Tables[0];

                foreach (DataRow row in dtUser.Rows)
                {
                    _userInfo.Username = row["Username"].ToString();
                    _userInfo.Password = row["Password"].ToString();
                    _userInfo.FullName = row["FullName"].ToString();
                    _userInfo.Email = row["Email"].ToString();
                    _userInfo.Phone = row["Phone"].ToString();
                    _userInfo.DateCreate = DateTime.Parse(row["DateCreate"].ToString());
                    _userInfo.Role = int.Parse(row["Role"].ToString());
                    _userInfo.Status = int.Parse(row["Status"].ToString());
                    _userInfo.Address = row["Address"].ToString();
                }
                return _userInfo;
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable SelectAll(int id)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@UserId", id);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.Text, "SELECT * FROM Users WHERE UserId NOT IN (@UserId)", spr);
                return ds.Tables[0];
            }
            finally
            {
                _connection.Close();
            }
        }


        public bool Delete(string user)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@Username", user);
                int i = SqlHelper.ExecuteNonQuery(_connection, CommandType.StoredProcedure, "SP_Delete_User", spr);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            finally
            {
                _connection.Close();
            }
        }

        public bool Update(UserInfo user)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[6];
                spr[0] = new SqlParameter("@Username", user.Username);
                spr[1] = new SqlParameter("@FullName", user.FullName);
                spr[2] = new SqlParameter("@Email", user.Email);
                spr[3] = new SqlParameter("@Phone", user.Phone);
                spr[4] = new SqlParameter("@Address", user.Address);
                spr[5] = new SqlParameter("@Role", user.Role);
                int i = SqlHelper.ExecuteNonQuery(_connection, CommandType.StoredProcedure, "SP_Update_User", spr);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            finally
            {
                _connection.Close();
            }
        }


        public UserInfo SelectByUserName(string user)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[1];
                spr[0] = new SqlParameter("@Username", user);
                DataSet ds = SqlHelper.ExecuteDataset(_connection, CommandType.StoredProcedure, "[SP_SelectByUserName_User]", spr);

                DataTable table = ds.Tables[0];
                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        _userInfo = new UserInfo();

                        _userInfo.Username = row["Username"].ToString();
                        _userInfo.Password = row["Password"].ToString();
                        _userInfo.FullName = row["FullName"].ToString();
                        _userInfo.Address = row["Address"].ToString();
                        _userInfo.Phone = row["Phone"].ToString();
                        _userInfo.Email = row["Email"].ToString();
                        _userInfo.DateCreate = DateTime.Parse(row["DateCreate"].ToString());
                        _userInfo.Role = int.Parse(row["Role"].ToString());
                    }
                    return _userInfo;
                }
                return null;
            }
            finally
            {
                _connection.Close();
            }
        }

        public bool Insert(UserInfo user)
        {

            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[8];
                spr[0] = new SqlParameter("@Username", user.Username);
                spr[1] = new SqlParameter("@Password", user.Password);
                spr[2] = new SqlParameter("@FullName", user.FullName);
                spr[3] = new SqlParameter("@Email", user.Email);
                spr[4] = new SqlParameter("@Phone", user.Phone);
                spr[5] = new SqlParameter("@Address", user.Address);
                spr[6] = new SqlParameter("@DateCreate", user.DateCreate);
                spr[7] = new SqlParameter("@Role", user.Role);
                int i = SqlHelper.ExecuteNonQuery(_connection, CommandType.StoredProcedure, "[SP_Insert_User]", spr);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            finally
            {
                _connection.Close();
            }
        }

        public bool ResetPass(string user, string pass)
        {
            try
            {
                _connection = GetConnection();
                SqlParameter[] spr = new SqlParameter[2];
                spr[0] = new SqlParameter("@Username", user);
                spr[1] = new SqlParameter("@NewPass", pass);

                int i = SqlHelper.ExecuteNonQuery(_connection, CommandType.StoredProcedure, "[SP_ResetPass_User]", spr);
                if (i > 0)
                {
                    return true;
                }
                return false;
            }
            finally
            {
                _connection.Close();
            }
        }
    }
}